﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using CardLogistics.Model.PutOutCard;

namespace CardLogistics.DAL.PutOutCard
{
    /// <summary>
    /// 半成品/成品卡发放_数据访问类
    /// </summary>
    public class PutOutSendCardData
    {
        #region 查询
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetList(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "v_PutOutSendCard", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据编号查询基本信息
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoById(string id)
        {
            return GetList(" ID = '" + id + "' ", "*");
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PutOutSendCard GetModel(string rId)
        {
            PutOutSendCard model = new PutOutSendCard();
            DataRow row = GetInfoById(rId).Rows[0];
            if (row != null)
            {
                if (row["ID"] != null)
                {
                    model.ID = row["ID"].ToString();
                }
                if (row["SENDNO"] != null)
                {
                    model.SENDNO = row["SENDNO"].ToString();
                }
                if (row["LEADINGUNIT"] != null)
                {
                    model.LEADINGUNIT = row["LEADINGUNIT"].ToString();
                }
                if (row["LEADINGDATE"] != null && row["LEADINGDATE"].ToString() != "")
                {
                    model.LEADINGDATE = DateTime.Parse(row["LEADINGDATE"].ToString());
                }
                if (row["REMARKS"] != null)
                {
                    model.REMARKS = row["REMARKS"].ToString();
                }
                if (row["LASTUPDATEUSER"] != null)
                {
                    model.LASTUPDATEUSER = row["LASTUPDATEUSER"].ToString();
                }
                if (row["LASTUPDATEDATE"] != null && row["LASTUPDATEDATE"].ToString() != "")
                {
                    model.LASTUPDATEDATE = DateTime.Parse(row["LASTUPDATEDATE"].ToString());
                }
                if (row["ISDELETE"] != null && row["ISDELETE"].ToString() != "")
                {
                    model.ISDELETE = decimal.Parse(row["ISDELETE"].ToString());
                }
                if (row["ADDTIME"] != null && row["ADDTIME"].ToString() != "")
                {
                    model.ADDTIME = DateTime.Parse(row["ADDTIME"].ToString());
                }
                if (row["PUTOUTCARDID"] != null)
                {
                    model.PUTOUTCARDID = row["PUTOUTCARDID"].ToString();
                }
                if (row["ISSUBMIT"] != null && row["ISSUBMIT"].ToString() != "")
                {
                    model.ISSUBMIT = decimal.Parse(row["ISSUBMIT"].ToString());
                }
                if (row["LEADINGPERSON"] != null)
                {
                    model.LEADINGPERSON = row["LEADINGPERSON"].ToString();
                }

                if (row["SalesName"] != null)
                {
                    model.SalesName = row["SalesName"].ToString();
                }
                if (row["PutoutCardNo"] != null)
                {
                    model.PutoutCardNo = row["PutoutCardNo"].ToString();
                }

            }
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(PutOutSendCard model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
				    new OracleParameter("vSENDNO", OracleType.NVarChar),
					new OracleParameter("vLEADINGUNIT", OracleType.NVarChar),
					new OracleParameter("vLEADINGDATE", OracleType.DateTime),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vPUTOUTCARDID", OracleType.VarChar,50),
					new OracleParameter("vISSUBMIT", OracleType.Number,4),
                    new OracleParameter("vLEADINGPERSON", OracleType.NVarChar)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.SENDNO;
                parameters[2].Value = model.LEADINGUNIT;
                parameters[3].Value = model.LEADINGDATE;
                parameters[4].Value = model.REMARKS;
                parameters[5].Value = model.LASTUPDATEUSER;
                parameters[6].Value = model.LASTUPDATEDATE;
                parameters[7].Value = model.ISDELETE;
                parameters[8].Value = model.ADDTIME;
                parameters[9].Value = model.PUTOUTCARDID;
                parameters[10].Value = model.ISSUBMIT;
                parameters[11].Value = model.LEADINGPERSON;

                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutSendCard_Add", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载新增信息
        /// <summary>
        /// 重载新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(PutOutSendCard model, string FileValue)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数
                StringBuilder strSql = new StringBuilder();
                try
                {
                    string gid = System.Guid.NewGuid().ToString();//主键Id
                    OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
				    new OracleParameter("vSENDNO", OracleType.NVarChar),
					new OracleParameter("vLEADINGUNIT", OracleType.NVarChar),
					new OracleParameter("vLEADINGDATE", OracleType.DateTime),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vPUTOUTCARDID", OracleType.VarChar,50),
					new OracleParameter("vISSUBMIT", OracleType.Number,4),
                    new OracleParameter("vLEADINGPERSON", OracleType.NVarChar),
                    new OracleParameter("vID",OracleType.VarChar)};
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Value = model.SENDNO;
                    parameters[2].Value = model.LEADINGUNIT;
                    parameters[3].Value = model.LEADINGDATE;
                    parameters[4].Value = model.REMARKS;
                    parameters[5].Value = model.LASTUPDATEUSER;
                    parameters[6].Value = model.LASTUPDATEDATE;
                    parameters[7].Value = model.ISDELETE;
                    parameters[8].Value = model.ADDTIME;
                    parameters[9].Value = model.PUTOUTCARDID;
                    parameters[10].Value = model.ISSUBMIT;
                    parameters[11].Value = model.LEADINGPERSON;
                    parameters[12].Value = gid;

                    strSql.Append(" begin  ");
                    strSql.Append(" delete from putoutsendcard_detail where sendcardid ='" + gid + "';");
                    for (int i = 0; i < count; i++)
                    {
                        string cardno = split[i].Split('|')[0];
                        string iniflag = split[i].Split('|')[1] == "否" ? "0" : "1";
                        string fgvamt = split[i].Split('|')[2];
                        string precharge = split[i].Split('|')[3];
                        string startcardno = split[i].Split('|')[4];
                        string endcardno = split[i].Split('|')[5];
                        string duenumber = split[i].Split('|')[6];
                        string actualnumber = split[i].Split('|')[7];
                        string missingcardno = split[i].Split('|')[8];//缺卡卡号

                        strSql.Append(" insert into putoutsendcard_detail");
                        strSql.Append(" (sendcardid,cardno,iniflag,fgvamt,precharge,startcardno,endcardno,duenumber,actualnumber,missingcardno)");
                        strSql.Append(" values(");
                        strSql.Append("'" + gid + "',");
                        strSql.Append("" + cardno + "," + iniflag + "," + fgvamt + "," + precharge + "," + startcardno + "," + endcardno + ",");
                        strSql.Append("" + duenumber + "," + actualnumber + "," + missingcardno + "");
                        strSql.Append(");");

                        #region 插入卡明细
                        strSql.Append("P_CARDRECORD_Add(" + startcardno + "," + endcardno + ",'0',0,'" + gid + "',");
                        strSql.Append("to_date('" + model.LEADINGDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),5," + i + ",0);");
                        #endregion
                    }
                    strSql.Append(" end; ");
                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }
                    StrOutPar = DbHelperSQL.RunProcedure("P_PutOutSendCard_Add", parameters, parameters[0]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                        tx.Commit();
                    else
                        tx.Rollback();

                }
                catch (System.Data.OracleClient.OracleException e)
                {

                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return StrOutPar;
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(PutOutSendCard model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("vID", OracleType.VarChar,50),
				    new OracleParameter("vSENDNO", OracleType.NVarChar),
					new OracleParameter("vLEADINGUNIT", OracleType.NVarChar),
					new OracleParameter("vLEADINGDATE", OracleType.DateTime),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vPUTOUTCARDID", OracleType.VarChar,50),
					new OracleParameter("vISSUBMIT", OracleType.Number,4),
                    new OracleParameter("vLEADINGPERSON", OracleType.NVarChar),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                parameters[0].Value = model.ID;
                parameters[1].Value = model.SENDNO;
                parameters[2].Value = model.LEADINGUNIT;
                parameters[3].Value = model.LEADINGDATE;
                parameters[4].Value = model.REMARKS;
                parameters[5].Value = model.LASTUPDATEUSER;
                parameters[6].Value = model.LASTUPDATEDATE;
                parameters[7].Value = model.ISDELETE;
                parameters[8].Value = model.ADDTIME;
                parameters[9].Value = model.PUTOUTCARDID;
                parameters[10].Value = model.ISSUBMIT;
                parameters[11].Value = model.LEADINGPERSON;
                parameters[12].Direction = ParameterDirection.Output;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutSendCard_Update", parameters, parameters[12]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载修改信息
        /// <summary>
        /// 重载修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(PutOutSendCard model, string FileValue)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数
                StringBuilder strSql = new StringBuilder();
                try
                {
                    OracleParameter[] parameters = {
                    new OracleParameter("vID", OracleType.VarChar,50),
				    new OracleParameter("vSENDNO", OracleType.NVarChar),
					new OracleParameter("vLEADINGUNIT", OracleType.NVarChar),
					new OracleParameter("vLEADINGDATE", OracleType.DateTime),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vISDELETE", OracleType.Number,4),
					new OracleParameter("vADDTIME", OracleType.DateTime),
					new OracleParameter("vPUTOUTCARDID", OracleType.VarChar,50),
					new OracleParameter("vISSUBMIT", OracleType.Number,4),
                    new OracleParameter("vLEADINGPERSON", OracleType.NVarChar),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.SENDNO;
                    parameters[2].Value = model.LEADINGUNIT;
                    parameters[3].Value = model.LEADINGDATE;
                    parameters[4].Value = model.REMARKS;
                    parameters[5].Value = model.LASTUPDATEUSER;
                    parameters[6].Value = model.LASTUPDATEDATE;
                    parameters[7].Value = model.ISDELETE;
                    parameters[8].Value = model.ADDTIME;
                    parameters[9].Value = model.PUTOUTCARDID;
                    parameters[10].Value = model.ISSUBMIT;
                    parameters[11].Value = model.LEADINGPERSON;
                    parameters[12].Direction = ParameterDirection.Output;

                    strSql.Append(" begin  ");
                    strSql.Append(" delete from putoutsendcard_detail where sendcardid ='" + model.ID + "';");
                    for (int i = 0; i < count; i++)
                    {
                        string cardno = split[i].Split('|')[0];
                        string iniflag = split[i].Split('|')[1] == "否" ? "0" : "1";
                        string fgvamt = split[i].Split('|')[2];
                        string precharge = split[i].Split('|')[3];
                        string startcardno = split[i].Split('|')[4];
                        string endcardno = split[i].Split('|')[5];
                        string duenumber = split[i].Split('|')[6];
                        string actualnumber = split[i].Split('|')[7];
                        string missingcardno = split[i].Split('|')[8];//缺卡卡号

                        strSql.Append(" insert into putoutsendcard_detail");
                        strSql.Append(" (sendcardid,cardno,iniflag,fgvamt,precharge,startcardno,endcardno,duenumber,actualnumber,missingcardno)");
                        strSql.Append(" values(");
                        strSql.Append("'" + model.ID + "',");
                        strSql.Append("" + cardno + "," + iniflag + "," + fgvamt + "," + precharge + "," + startcardno + "," + endcardno + ",");
                        strSql.Append("" + duenumber + "," + actualnumber + "," + missingcardno + "");
                        strSql.Append(");");

                        #region 插入卡明细
                        strSql.Append("P_CARDRECORD_Add(" + startcardno + "," + endcardno + "," + missingcardno + ",0,'" + model.ID + "',");
                        strSql.Append("to_date('" + model.LEADINGDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),5," + i + ",0);");
                        #endregion
                    }
                    strSql.Append(" end; ");
                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }
                    StrOutPar = DbHelperSQL.RunProcedure("P_PutOutSendCard_Update", parameters, parameters[12]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                        tx.Commit();
                    else
                        tx.Rollback();
                }
                catch (System.Data.OracleClient.OracleException e)
                {

                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return StrOutPar;
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id">删除信息的ID</param>
        /// <returns>删除成功返回True。否则返回false</returns>
        public string Delete(string id)
        {
            try
            {
                OracleParameter[] parameters = {
                        new OracleParameter("varOut",  OracleType.VarChar,100),
					    new OracleParameter("vid",  OracleType.VarChar,50)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = id;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutOutSendCard_Delete", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据出库单号、编辑时的ID 返回 盒号、开始卡号等信息
        /// <summary>
        /// 根据出库单号、编辑时的ID 返回 盒号、开始卡号等信息
        /// </summary>
        /// <param name="putOutId">出库单主键</param>
        /// <param name="eId">编辑是的主键</param>
        /// <returns></returns>
        public DataTable GetBoxInfo(string putOutId, string eId)
        {
            StringBuilder sbInfo = new StringBuilder();
            sbInfo.Append(" select a.CARDNO,a.cardtypename,a.suppliersname,a.suppid,a.ActualNumber,a.FlagName,a.Fgvamt,a.precharge,a.EndCardNo,");
            sbInfo.Append(" (select boxno from cardmaking_cardoutstrgedetail cr where cr.cardno=a.CARDNO) as boxno, ");
            sbInfo.Append(" case length(LENDCARDNO) when 8 then to_number(LENDCARDNO)+1 else to_number(a.StartCardNo) end StartCardNo,s.checkid,p.ID as zkId");
            sbInfo.Append(" from v_putoutcard_detail a left join ");
            sbInfo.Append(" (select sum(ActualNumber) lACTUALNUMBER,CARDNO,max(ENDCARDNO) LENDCARDNO,min(issubmit) bissubmit from v_putoutsendcard_detail where PUTOUTCARDID='" + putOutId + "' and issubmit<>4");
            if (eId != "")//编辑时排除本条记录的计算
                sbInfo.Append(" and SENDCARDID!='" + eId + "'");
            sbInfo.Append(" group by CARDNO) b on a.CARDNO=b.CARDNO");
            sbInfo.Append(" left join v_storage_check_card_detailed s on s.cardno=a.CARDNO");//关联验收入库表获取验收入库ID
            sbInfo.Append(" left join v_putincarddetailedrpt p on p.CARDNO =a.cardno");//关联制卡入库表获取制卡入库ID
            sbInfo.Append(" where a.ActualNumber-nvl(b.lACTUALNUMBER,0)>0");
            sbInfo.Append(" and a.putOutCardNo in(select putOutCardNo from v_Putoutcard where id='" + putOutId + "' )");//根据审批单的出库单号查询可用的发放信息
            if (putOutId == "")//新增时加过滤条件
            sbInfo.Append(" and a.CARDNO not in (select * from v_getretcardno )");//过滤已经退货的卡号
            sbInfo.Append(" and nvl(bissubmit,100)>3");
            return DbHelperSQL.GetQueryResult(sbInfo.ToString());
        }
        #endregion
    }
}
